/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Dr.Tree
 * Created On : Dec 6, 2011  4:49:36 PM 
 * e-Mail : chatree@tis.co.th
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : promis.api.excel.read
 * File Name : ReadExcel.java
 * TODO
 */
package promis.api.excel.read;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import promis.api.oracle.TableServices;
import promis.db.connection.dbHelper;
import promis.utility.CommonService;
import promis.utility.Utilitys;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * @author chatree
 *
 */
public class ReadExcel {
	
	private static String inputFile;
	public void setInputFile(String inputFile) {
		this.inputFile = inputFile;
	}

	/**
	 * 
	 * @throws Exception
	 */
	private static void CreateTemp(int total_columns, String table_temp)  {
		try{
			StringBuilder _strQuery = new StringBuilder();
			_strQuery.append(" CREATE TABLE "+ table_temp +" ( ");
			_strQuery.append(" FIELD_ID         INTEGER, ");
			
			for(int i = 0; i < total_columns; i++){
				_strQuery.append(" FIELD_"+ Utilitys.leftPad((i+1)+"", 3, "0") +"    VARCHAR2(2000), ");	
			}
			_strQuery.append(" ISACTIVE         VARCHAR2(1) ");
			_strQuery.append(" ) ");
			
			System.out.println(_strQuery.toString());
			
			Connection conn = new dbHelper().getConn();
			conn.setAutoCommit(true);
			Statement stmt =  conn.createStatement();
			stmt.executeUpdate(_strQuery.toString());
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public void read() throws IOException {
		File inputWorkbook = new File(ReadExcel.inputFile);
		Workbook w;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines			
			
			// Create temp
			CreateTemp(sheet.getColumns(),"");
			
			for (int j = 0; j < sheet.getColumns(); j++) {
								
				for (int i = 0; i < sheet.getRows(); i++) {
					Cell cell = sheet.getCell(j, i);
					
					if(i == 0) System.out.println("Header value "+ cell.getContents());
					
					CellType type = cell.getType();
					if (cell.getType() == CellType.LABEL) {
						System.out.println("I got a label " + cell.getContents());
					}

					if (cell.getType() == CellType.NUMBER) {
						System.out.println("I got a number " + cell.getContents());
					}
				}
			}
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}
	
	private void Readding(String table_temp) {
		File inputWorkbook = new File(inputFile);
		Workbook w;
		try{			
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines	
						
			Connection conn = new dbHelper().getConn();
			conn.setAutoCommit(true);
			
			for (int i = 0; i < sheet.getRows(); i++) {
				if(i != 0){
					String sql = "INSERT INTO "+ table_temp +" (FIELD_ID, FIELD_001, FIELD_002, FIELD_003, FIELD_004, FIELD_005, FIELD_006, ISACTIVE) ";
					sql += " VALUES ( " + i + "";
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = sheet.getCell(j, i);
						sql += ", "+ Utilitys.strSql(cell.getContents());
					}
					sql += " , 'Y' ) ";
					System.out.println(sql + "\n");
										
					Statement stmt = conn.createStatement();
					stmt.executeUpdate(sql);
				}
			}
			conn.close();
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * @param table_temp
	 * @throws Exception
	 */
	public void CreateTable(String table_temp) throws Exception {
		File inputWorkbook = new File(inputFile);
		Connection conn = new dbHelper().getConn();
		conn.setAutoCommit(true);
		
		Workbook w;
		try{			
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines
			
			if(new TableServices().TableExists("SYNCHRO", table_temp))
				throw new Exception("Exception :: OWNER=SYNCHRO, "+ table_temp +"=TableExists");
			
			StringBuffer _strQuery = new StringBuffer();			
			_strQuery.append(" CREATE TABLE "+ table_temp +" ( ");
			_strQuery.append(" FIELD_ID         INTEGER ");
			
			int column_int = 0;
			if(sheet.getRows() > 0)
			{
				int i = 0; //Row Hrader
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = sheet.getCell(j, i);
						String column_header_name = CommonService.IsNullRString(cell.getContents()).replace(" ", "");
						if(CommonService.IsNull(column_header_name)){
							column_int++;
							_strQuery.append(" ,FIELD_"+ Utilitys.leftPad((column_int + 1)+"", 3, "0") +"    VARCHAR2(2000), ");
						}else{
							_strQuery.append(" ,"+ column_header_name +"    VARCHAR2(2000) ");
						}
					}
					
					_strQuery.append(", ISACTIVE         VARCHAR2(1) ");
					_strQuery.append(" ) ");
					
					System.out.println(_strQuery.toString() + "\n");
					
					Statement stmt = conn.createStatement();
					stmt.executeUpdate(_strQuery.toString());
			}
						
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(!conn.isClosed())
				conn.close();
			
			conn = null;
		}
	}
	
	private void ReaddingUpdate() {
		File inputWorkbook = new File(inputFile);
		Workbook w;
		try{			
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines
			
			Connection conn = new dbHelper().getConn();
			conn.setAutoCommit(true);
			
			String sql = "SELECT * FROM  EXECL_TEMP1";
			Statement stmt =  conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				/*
				sql = " UPDATE SPL.permanent_receipts SET SYNC_ISSTAGING = 'N' ";
				sql += " WHERE  PROJECT = " + Utilitys.strSql(rs.getString("FIELD_001")); 
				sql += " AND (RECEIPT_TYPE || '-' || RECEIPT_SERIES || '-' || TO_CHAR (RECEIPT_NUMBER, 'fm000009')) = " + Utilitys.strSql(rs.getString("FIELD_003"));
				
				Statement stmt1 =  conn.createStatement();
				stmt1.executeUpdate(sql);
				*/
			}
			conn.close();
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static void ReadFormExcel() throws IOException{		
		ReadExcel test = new ReadExcel();
		test.setInputFile("C:\\Users\\chatree\\workspace\\ExcelTested\\excel_store\\FI_ARRECEIPTRE_OUTB_2013-03-15.xls");
		
		try {
			
			test.CreateTable("EXCEL_TEMP1");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws IOException {		
		try
		{
			new ReadExcel().ReadFormExcel();
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
